﻿namespace SGLL
{
    class ClassSQL
    {
        //Users
        public static string GetUsers = "[SGLL].[dbo].[PROCEDURE_USERS_CONSULTATION];";
        //Calls
        public static string SetUpDateCalls = "[SGLL].[dbo].[PROCEDURE_CALLS] @0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11;";
        public static string GetCallsFiltered = "[SGLL].[dbo].[PROCEDURE_CALLS_CONSULTATION] @0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11;";
        public static string GetCallByID = "[dbo].[PROCEDURE_CALLS_FILTERED_BY_ID] {0};";
        public static string GetNumberOfCallsToday = "SELECT SUM([QUANTITY]) FROM (SELECT COUNT(*) AS [QUANTITY] FROM [CALLS] WHERE CONVERT(DATE, [INSERTED_DATE]) = CONVERT(DATE, GETDATE()) UNION SELECT COUNT(*) AS [QUANTITY] FROM [COMPLAINTS] WHERE CONVERT(DATE, [INSERTED]) = CONVERT(DATE, GETDATE())) TEMP;";
        public static string GetLastCall = "SELECT TOP 1 [LAST] FROM (SELECT [INSERTED_DATE] AS [LAST] FROM [CALLS] WHERE [ID] = IDENT_CURRENT('[CALLS]') UNION SELECT [INSERTED] AS [LAST] FROM [COMPLAINTS] WHERE [ID] = IDENT_CURRENT('[COMPLAINTS]')) TEMP ORDER BY [LAST] DESC;";
        public static string IsCallOpen = "IF EXISTS(SELECT [ID] FROM [SGLL].[dbo].[CALLS] WHERE [ID] = {0} AND [CLOSE_BY_USER] IS NULL) SELECT 1 ELSE SELECT 0;";
        //Complaints
        public static string SetUpComplaints = "[SGLL].[dbo].[PROCEDURE_COMPLAINTS] @0, @1, @2, @3, @4, @5, @6, @7;";
        public static string IsComplaintOpen = "IF EXISTS(SELECT [ID] FROM [SGLL].[dbo].[COMPLAINTS] WHERE [ID] = {0} AND [CLOSE_BY_USER] IS NULL) SELECT 1 ELSE SELECT 0;";
        //Doctors
        public static string GetDoctors = "[SGLL].[dbo].[PROCEDURE_DOCTORS_CONSULTATION] '{0}';";
        public static string GetDoctorsByID = "[SGLL].[dbo].[PROCEDURE_DOCTORS_FILTERED_BY_ID] '{0}';";
        //Route
        public static string GetRoute = "[SGLL].[dbo].[PROCEDURE_ROUTE_CONSULTATION];";
        //IDs
        public static string GetNextID = "SELECT IDENT_CURRENT('{0}') + 1;";
        //Types Of Samples
        public static string GetTypesOfSamplesByDescription = "SELECT * FROM [SGLL].[dbo].[TYPES_OF_SAMPLES] WHERE [DESCRIPTION] = '{0}';";
        public static string SetUpTypesOfSamples = "[SGLL].[dbo].[PROCEDURE_INSERT_UPDATE_TYPES_OF_SAMPLES] @0, @1, @2;";
    }
}